import logging
import math
from decimal import Decimal
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.engine import create
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

mm_head_router = APIRouter(prefix="/maintain/mmHead", tags=["保养计划管理"], dependencies=[Depends(oauth2_scheme)])


class MaintenancePlanSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    ftakedateStart: Optional[str] = Field("", description="计划开始日期(yyyyMMdd)")
    ftakedateEnd: Optional[str] = Field("", description="计划结束日期(yyyyMMdd)")
    fpointNo: Optional[str] = Field("", description="点位编号")
    fbillno: Optional[str] = Field("", description="单据编号（支持前缀匹配）")
    fmmuser: Optional[str] = Field("", description="保养人员")
    fmmtypeno: Optional[str] = Field("", description="保养类型编号")
    fbillstate: Optional[str] = Field("", description="单据状态编码")


@mm_head_router.get("/list", summary="保养计划查询接口")
def list_mm_head(search: MaintenancePlanSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.s_tc_mm_head where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fmmuser != "":
        sql = sql + " and fmmuser = :fmmuser "
        param["fmmuser"] = search.fmmuser
    if search.fmmtypeno != "":
        sql = sql + " and fmmtypeno = :fmmtypeno "
        param["fmmtypeno"] = search.fmmtypeno
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(fmmdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fmmdate,fsite_no,fpoint_no,fequipmentbarcode,feuipmentid,fmmtypeno,fmmlevelno,"
    sql = sql + " fmmworktime,fmmuser,fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate"
    sql = sql + " from lims.s_tc_mm_head where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fmmuser != "":
        sql = sql + " and fmmuser = :fmmuser "
        param["fmmuser"] = search.fmmuser
    if search.fmmtypeno != "":
        sql = sql + " and fmmtypeno = :fmmtypeno "
        param["fmmtypeno"] = search.fmmtypeno
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(fmmdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    if search.order == "desc":
        sql = sql + " order by  fmmdate,fbillno desc "
    else:
        sql = sql + " order by  fmmdate,fbillno "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fmmitemno,b.fmmflag,b.fmmresult,b.fmemo"
    sql_3 = sql_3 + " from lims.s_tc_mm_head a,lims.s_tc_mm_detail b where a.fbillno=b.fbillno "
    if search.fsiteNo != "":
        sql_3 = sql_3 + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fpointNo != "":
        sql_3 = sql_3 + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fmmuser != "":
        sql_3 = sql_3 + " and fmmuser = :fmmuser "
        param["fmmuser"] = search.fmmuser
    if search.fmmtypeno != "":
        sql_3 = sql_3 + " and fmmtypeno = :fmmtypeno "
        param["fmmtypeno"] = search.fmmtypeno
    if search.fbillstate != "":
        sql_3 = sql_3 + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(fmmdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['ftakeuser'] = data[key][2]
        d1['ftakedate'] = data[key][3]
        d1['fpointno'] = data[key][4]
        d1['fitemcount'] = data[key][5]
        d1['fpiececount'] = data[key][6]
        d1['ftotalamt'] = data[key][7]
        d1['fexplanation'] = data[key][8]
        d1['fupddate'] = data[key][9]
        d1['foperator'] = data[key][10]
        d1['fcheckdate'] = data[key][11]
        d1['fcheckuser'] = data[key][12]
        d1['fbillstate'] = data[key][13]
        d1['fbilltype'] = data[key][14]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fmmitemno'] = data_detail[j1][2]
                d2['fmmflag'] = data_detail[j1][3]
                d2['fmmresult'] = data_detail[j1][4]
                d2['fmemo'] = data_detail[j1][5]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class MaintenancePlanDetail(BaseModel):
    fline: int = Field(..., description="明细行号")
    fmmitemno: str = Field(..., description="保养项目编号")
    fmmflag: int = Field(..., description="保养标志")
    fmmresult: str = Field(..., description="保养结果")
    fmemo: str = Field(..., description="备注")
    fqty: Decimal = Field(0, description="数量")
    ftotamt: Decimal = Field(0, description="总金额")


class MaintenancePlanCreate(BaseModel):
    """保养计划新增请求参数"""
    fbillno: str = Field(..., description="单据编号")
    fbillstate: str = Field(..., description="单据状态编码")
    feuipmentid: str = Field(..., description="设备ID")
    fequipmentbarcode: str = Field(..., description="设备条码")
    fequipmenttag: str = Field(..., description="设备标签")
    fmmdate: str = Field(..., description="保养日期(yyyy-MM-dd)")
    fmmlevelno: str = Field(..., description="保养等级编号")
    fmmtypeno: str = Field(..., description="保养类型编号")
    fmmuser: str = Field(..., description="保养人员")
    fmmworktime: str = Field(..., description="保养工时")
    fpointNo: str = Field(..., description="点位编号")
    fsiteNo: str = Field(..., description="站点编号")
    foperator: str = Field(..., description="操作人")
    fupddate: str = Field(..., description="更新日期(yyyy-MM-dd HH:mm:ss)")
    details: List[MaintenancePlanDetail] = Field(..., description="保养计划明细列表")
    fexplanation: str = Field("", description="注释")


@mm_head_router.post("/create", summary="保养计划新增接口")
def create_mm_head(create: MaintenancePlanCreate, db: Session = Depends(get_db)):
    try:
        fbillno = create.fbillno
        # 返回内容
        d1 = {}
        detail_list = []
        with db.begin():
            jsondata = create.details
            for data in jsondata:
                sql = "insert into lims.s_tc_mm_detail(fbillno,fline,fmmitemno,fmmflag,fmmresult,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fmmitemno , :fmmflag , :fmmresult , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fmmitemno": data.fmmitemno,
                    "fmmflag": data.fmmflag,
                    "fmmresult": data.fmmresult,
                    "fmemo": data.fmemo
                })

                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['fmmitemno'] = data.fmmitemno
                d2['fmmflag'] = data.fmmflag
                d2['fmmresult'] = data.fmmresult
                d2['fmemo'] = data.fmemo
                detail_list.append(d2)
            sql = "insert into lims.s_tc_mm_head(fbillno,fsite_no,fpoint_no,fmmdate,fequipmentbarcode,feuipmentid,fmmtypeno,fmmlevelno,fmmworktime,fmmuser,fupddate,foperator,fbillstate)"
            sql = sql + " values( :fbillno , :fsiteNo , :fpointNo , :fmmdate , :fequipmentbarcode , :feuipmentid , :fmmtypeno , :fmmlevelno , :fmmworktime , :fmmuser , :fupddate , :foperator , :fbillstate )"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": create.fsiteNo,
                "fpointNo": create.fpointNo,
                "fmmdate": create.fmmdate,
                "fequipmentbarcode": create.fequipmentbarcode,
                "feuipmentid": create.feuipmentid,
                "fmmtypeno": create.fmmtypeno,
                "fmmlevelno": create.fmmlevelno,
                "fmmworktime": create.fmmworktime,
                "fmmuser": create.fmmuser,
                "fupddate": create.fupddate,
                "foperator": create.foperator,
                "fbillstate": create.fbillstate,
            })
            # 返回
            d1['fbillno'] = fbillno
            d1['fsiteNo'] = create.fsiteNo
            d1['fsiteName'] = None
            d1['fpointNo'] = create.fpointNo
            d1['fequipmentbarcode'] = create.fequipmentbarcode
            d1['fequipmenttag'] = create.fequipmenttag
            d1['feuipmentid'] = create.feuipmentid
            d1['fmmdate'] = create.fmmdate
            d1['fmmlevelname'] = None
            d1['fmmlevelno'] = create.fmmlevelno
            d1['fmmtypename'] = None
            d1['fmmtypeno'] = create.fmmtypeno
            d1['fmmuser'] = create.fmmuser
            d1['fmmworktime'] = float(create.fmmworktime)
            d1['fexplanation'] = None
            d1['fupddate'] = create.fupddate
            d1['foperator'] = create.foperator
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbillstate'] = create.fbillstate
            d1['flabname'] = None
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@mm_head_router.post("/update", summary="保养计划修改接口")
def update_mm_head(update: MaintenancePlanCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = update.fbillno
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "delete from lims.s_tc_mm_detail where fbillno= :fbillno "
            db.execute(text(sql), {
                "fbillno": fbillno,
            })
            jsondata = update.details
            for data in jsondata:
                sql = "insert into lims.s_tc_mm_detail(fbillno,fline,fmmitemno,fmmflag,fmmresult,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fmmitemno , :fmmflag , :fmmresult , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fmmitemno": data.fmmitemno,
                    "fmmflag": data.fmmflag,
                    "fmmresult": data.fmmresult,
                    "fmemo": data.fmemo,
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + data.fqty
                ftotalamt = ftotalamt + data.ftotamt
            sql = "update lims.s_tc_mm_head "
            sql = sql + " set fexplanation=:fexplanation "
            sql = sql + ",fupddate=now(),foperator= :userid "
            sql = sql + " where fbillno= :fbillno "
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fexplanation": update.fexplanation,
                "userid": user_id
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@mm_head_router.post("/delete/{fbillno}", summary="保养计划删除接口")
def delete_mm_head(fbillno: str = Path(description="保养计划单号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select count(1) from lims.s_tc_mm_head where fbillno= :fbillno and fbillstate>1"
            data = db.execute(text(sql), {
                "fbillno": fbillno,
            }).fetchall()
            if data[0][0] > 0:
                return BaseResponse.error(message="该订单不能删除")
            else:
                sql = "delete from lims.s_tc_mm_detail2 where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                sql = "delete from lims.s_tc_mm_detail where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                sql = "delete from lims.s_tc_mm_head where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
